package jdbca;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class DeptTest {
    public List<Employee> getAll(){
        List<Employee> employees= new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/datt", "root", "1234");
        }catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "select empno,ename,job,mgr,hiredate,salary,comm,deptno from emp";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int empno = resultSet.getInt(1);
                String ename = resultSet.getString(2);
                String job = resultSet.getString(3);
                int managerNo = resultSet.getInt(4);
                LocalDate hiredate = resultSet.getDate(5).toLocalDate();

                float salary = resultSet.getFloat(6);
                float comm = resultSet.getFloat(7);
                int deptno = resultSet.getInt(8);
                Employee employee = new Employee(empno, ename, job, managerNo, hiredate, salary, comm, deptno);
                employees.add(employee);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return employees;
    }
    public Employee selectById(int empno){
        Employee employee = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/datt", "root", "1234");
        }catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "select empno,ename,job , hiredate,mgr,salary,comm, deptno from emp where empno = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,empno);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                empno = resultSet.getInt(1);
                String ename = resultSet.getString(2);
                String job = resultSet.getString(3);
                LocalDate hiredate = resultSet.getDate(4).toLocalDate();
                int mgr = resultSet.getInt(5);
                float salary = resultSet.getFloat(6);
                float comm = resultSet.getFloat(7);
                int deptno = resultSet.getInt(8);
                employee = new Employee(empno, ename, job, mgr, hiredate, salary, comm, deptno);

            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return employee;

    }

    //需求 通过java代码将部门数据插入到数据库中
    public int insert(Employee employee){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int rows = 0;

        //1.连接本地数据库
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String url = "jdbc:mysql://localhost:3306/datt";
        String user = "root";
        String password = "1234";
        try {
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("连接成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //2.执行sql语句，添加数据到dept表中
        // 将原来的SQL语句修改为明确指定列名
        String sql = "insert into emp (empno, ename, job, hiredate, mgr, salary, comm, deptno) values (?, ?, ?, ?, ?, ?, ?, ?)";

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, employee.getEmpno());
            preparedStatement.setString(2, employee.getEmpname());
            preparedStatement.setString(3, employee.getEmpJob());
            preparedStatement.setDate(4, Date.valueOf(employee.getEmpHiredate()));
            preparedStatement.setInt(5, employee.getManagerNo());
            preparedStatement.setFloat(6, employee.getEmpSalary());
            preparedStatement.setFloat(7, employee.getEmpComm());
            preparedStatement.setInt(8, employee.getDeptNo());
            rows = preparedStatement.executeUpdate();
            System.out.println(rows > 0 ? "添加成功" : "添加失败");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //3.关闭链接
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return rows;
    }
    public int update(Employee employee){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int rows = 0;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/acc", "root", "1234");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "update emp set ename = ?,job = ?,hiredate = ?,mgr = ?,salary = ?,comm = ?,deptno = ? where empno = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, employee.getEmpname());
            preparedStatement.setString(2, employee.getEmpJob());
            preparedStatement.setDate(3, Date.valueOf(employee.getEmpHiredate()));
            preparedStatement.setInt(4, employee.getManagerNo());
            preparedStatement.setFloat(5, employee.getEmpSalary());
            preparedStatement.setFloat(6, employee.getEmpComm());
            preparedStatement.setInt(7, employee.getDeptNo());
            preparedStatement.setInt(8, employee.getEmpno());
            rows = preparedStatement.executeUpdate();
            System.out.println(rows > 0 ? "修改成功" : "修改失败");
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return rows;
    }
    public int delete(int empno){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int rows = 0;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/acc", "root", "1234");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "delete from emp where empno = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, empno);
            rows = preparedStatement.executeUpdate();
            System.out.println(rows > 0 ? "删除成功" : "删除失败");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return rows;
    }
    public static void main(String[] args) {
        DeptTest deptTest = new DeptTest();
        Employee employee = new Employee();
        employee.setEmpno(1001);
        employee.setEmpname("张三");
        employee.setEmpJob("java");
        employee.setEmpHiredate(LocalDate.now());
        employee.setManagerNo(1002);
        employee.setEmpSalary(5000);
        employee.setEmpComm(500);
        employee.setDeptNo(10);
        System.out.println(deptTest.selectById(1011));
        System.out.println(deptTest.getAll());

    }

}